package my.dao.lmpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import my.bean.User;
import my.dao.IUserDao;
import my.dbUtil.DBUtils;

public class IUserDaoImpl implements IUserDao {
	private Connection conn;

	public IUserDaoImpl(Connection conn) {
		this.conn = conn;
	}

	public IUserDaoImpl() {

	}

	@Override
	public int addUser(User user) throws SQLException {
		int record = 0;
		PreparedStatement prestmt = null;

		try {
			String sql = "insert into users values(user_id.nextval,?,?,?,sysdate,?)";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, user.getAccount());
			prestmt.setString(2, user.getPassword());
			prestmt.setString(3, user.getEmail());
			prestmt.setString(4, user.getPhone());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("插入失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int deleteUserById(int id) throws SQLException {
		int record = 0;
		PreparedStatement prestmt = null;

		try {
			String sql = "delete from users where user_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("删除失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int modifyBaseMessage(User user) throws SQLException {
		int record = 0;
		PreparedStatement prestmt = null;

		try {
			String sql = "update users set user_email=?,user_phone=? where user_id=?";
			prestmt = conn.prepareStatement(sql);

			prestmt.setString(1, user.getEmail());
			prestmt.setString(2, user.getPhone());
			prestmt.setInt(3, user.getId());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("修改失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int modifyPasswordById(int id, String newPassword) throws SQLException {
		int record = 0;
		PreparedStatement prestmt = null;
		try {
			String sql = "update users set user_password=? where user_id=?";
			prestmt = conn.prepareStatement(sql);

			prestmt.setString(1, newPassword);
			prestmt.setInt(2, id);

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("修改失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public List<User> findAll() {
		List<User> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from users";
			prestmt = conn.prepareStatement(sql);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt(1);
				String account = rs.getString(2);
				String password = rs.getString(3).trim();
				String email = rs.getString(4);
				String phone = rs.getString(6);
				Timestamp regDate = rs.getTimestamp(5);

				User user = new User(id, account, password, email, phone, regDate);
				list.add(user);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public User findByAccount(String account) {
		User user = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from users where user_account like ?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, "%" + account + "%");

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt(1);
				String password = rs.getString(3).trim();
				String email = rs.getString(4);
				String phone = rs.getString(6);
				Timestamp regDate = rs.getTimestamp(5);

				user = new User(id, account, password, email, phone, regDate);
			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return user;
	}

	@Override
	public User findByUserId(int id) {
		User user = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from users where user_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			if (rs.next()) {
				String account = rs.getString(2);
				String password = rs.getString(3).trim();
				String email = rs.getString(4);
				String phone = rs.getString(6);
				Timestamp regDate = rs.getTimestamp(5);

				user = new User(id, account, password, email, phone, regDate);
			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return user;
	}

	@Override
	public User findByUserAccountAndPassword(String account, String password) {
		User user = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from users where user_account=? and trim(user_password)=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setString(1, account);
			prestmt.setString(2, password);

			rs = prestmt.executeQuery();

			if (rs.next()) {
				int id = rs.getInt(1);
				String email = rs.getString(4);
				String phone = rs.getString(6);
				Timestamp regDate = rs.getTimestamp(5);

				user = new User(id, account, password, email, phone, regDate);
			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return user;
	}

}
